import pymysql


class JD(object):
    def __init__(self):
        """初始化操作"""
        self.conn = pymysql.connect(host="192.168.199.132",port=3306,user="root", password="mysql",db="jing_dong",charset="utf8")

    def __del__(self):
        """清理操作"""
        # self.conn.close()
        pass

    def show_menu(self):
        """打印菜单　需要用户进行输入"""
        print("-" * 30)
        print('1. 查询所有商品信息')
        print("2. 查询所有包含商品的分类")
        print("3. 添加新商品分类")
        print("4. 将所有商品价格加1000")
        print("5. 将所有笔记本的分类改为超级本")
        print("6. 根据id查询商品信息")
        print("7. 根据id查询商品信息-防SQL注入")
        print("8. 退出系统")

    def get_all_info(self):
        """查询所有商品信息"""
        cur = self.conn.cursor()
        cur.execute("select * from goods")
        for line in cur.fetchall():
            print(line)
        cur.close()

    def get_info(self):
        '''查询所有包含商品的分类'''
        cur = self.conn.cursor()
        cur.execute("select name from goods_cates")
        for line in cur.fetchall():
            print(line)
        cur.close()

    def add_cate(self):
        '''添加新商品分类'''
        cur = self.conn.cursor()
        new_cate = input("请输入你要添加的商品分类:")
        cur.execute("insert into goods_cates(name) values (%s)",[new_cate])
        self.conn.commit()
        cur.close()

    def set_price(self):
        '''将所有商品价格加1000'''
        cur = self.conn.cursor()

        cur.execute("update goods set price = price + 1000")
        self.conn.commit()
        cur.close()

    def update_cate(self):

        '''将所有笔记本的分类改为超级本'''
        cur = self.conn.cursor()

        cur.execute("update goods set cate_name = (select id from goods_cates where name='超极本') where cate_name= (select id from goods_cates where name = '笔记本') ")

        self.conn.commit()
        cur.close()

    def get_info_by_id(self):
        '''根据id查询商品信息'''
        cur = self.conn.cursor()
        user_id = input("请输入你要查询的商品id:")
        cur.execute("select * from goods where id = %s" % user_id)
        print(cur.fetchall())
        cur.close()

    def get_info_by_id_safe(self):
        '''根据id查询商品信息-防SQL注入'''
        cur = self.conn.cursor()
        user_id = input("请输入你要查询的商品id:")
        cur.execute("select * from goods where id = %s",[user_id])
        print(cur.fetchall())
        cur.close()

    def main(self):
        while True:
            self.show_menu()
            data = input("请输入你要进行的操作：")
            if data == "1":
                self.get_all_info()
            elif data == "2":
                self.get_info()
            elif data == "3":
                self.add_cate()
            elif data == "4":
                self.set_price()
            elif data == "5":
                self.update_cate()
            elif data == "6":
                self.get_info_by_id()
            elif data == "7":
                self.get_info_by_id_safe()
            else:
                print("欢迎下次使用")
                break

if __name__ == '__main__':
    jd = JD()
    jd.main()